ALTER PROC sp_shipment_summary_report
@PRODUCT_IDS VARCHAR(50),
@CUSTOMER_ID INT,
@CATEGORY VARCHAR(50),
@PERIODICAL VARCHAR(50),
@DATE_FROM  VARCHAR(50),
@DATE_T0  VARCHAR(50),
@CURRENT_QUARTER VARCHAR(4),
@CURRENT_YEAR VARCHAR(4),
@DATE_RANGE  VARCHAR(10), 
@DEFAULT_PROMPT VARCHAR(20)

AS

BEGIN
	DECLARE @PROCESS_STEPS_TABLE TABLE( ProcessStep_ID INT )
	DECLARE @RESULT_BASEPRODUCTNAME VARCHAR(50)
	DECLARE @RESULT_BASE_PRODUCT_ID VARCHAR(20)
	DECLARE @RESULT_PROCESS_STEP_ID INT
	DECLARE @RESULT_PRODUCT_ID INT
	DECLARE @RESULT_PRODUCT VARCHAR(50)
	DECLARE @RESULT_PS_TYPE VARCHAR(50)
	DECLARE @RESULT_PRODUCT_TYPE VARCHAR(50)
	DECLARE @RESULT_PRODUCT_TYPE_ID INT
	DECLARE @RESULT_REQ_SCH_DATE DATETIME
	DECLARE @RESULT_SHIPPED_QTY BIGINT
	DECLARE @RESULT_UNIT_COST FLOAT
	DECLARE @RESULT_AMOUNT FLOAT
	
	DECLARE @LOT_BASEPRODUCTNAME VARCHAR(50)
	DECLARE @LOT_BASE_PRODUCT_ID VARCHAR(20)
	DECLARE @LOT_PROCESS_STEP_ID INT
	DECLARE @TEMP_LOT_PRODUCT_ID INT
	DECLARE @LOT_PRODUCT VARCHAR(50)
	DECLARE @LOT_PS_TYPE  VARCHAR(50)
	DECLARE @LOT_PRODUCT_TYPE  VARCHAR(50)
	DECLARE @LOT_PRODUCT_TYPE_ID INT

	DECLARE @REQUESTED_DATE DATETIME
	DECLARE @SCHEDULED_DATE DATETIME
	DECLARE @BOOKING_DATE DATETIME
		
	DECLARE @TEMP_CSLIT_ID INT
	DECLARE @TEMP_LINEITEM_ID INT
	DECLARE @TEMP_PRODUCT_ID INT	
	DECLARE @LINEITEM_PRODUCT_ID INT	
	DECLARE @TEMP_PS_ID INT 
	DECLARE @TEMP_UNIT_COST FLOAT
	DECLARE @TEMP_SHIPPED_QTY BIGINT
	DECLARE @TEMP_SHIPPED_DATE DATETIME
	DECLARE @TEMP_SHIPPED_PART_NO VARCHAR(200)

	DECLARE @LOT_ID INT	
	DECLARE @LOT_PRODUCT_ID INT
	DECLARE @LOT_PARENT_PRODUCT_ID INT
	DECLARE @PRODUCT_PARENT_PRODUCT_ID INT
	DECLARE @LINEITEM_PRODUCT_PARENT_PRODUCT_ID INT
	DECLARE @LOT_SHIPPED_DATE DATETIME
	DECLARE @LOT_SHIPPED_QTY BIGINT

	DECLARE @SHIP_REQ_SCH_DT DATETIME 
	DECLARE @SHIP_YR_I INT
	DECLARE @SHIP_MT_I INT
	DECLARE @SHIP_QT_I INT
	DECLARE @SHIP_QT_END_I INT
	DECLARE @ACT_YEAR INT
	DECLARE @CHK_PRODUCT_FOR_BOARD_OR_KIT VARCHAR(50)
	DECLARE @CHK_PRODUCT_FOR_BOARD_KIT_OR_CHIP VARCHAR(50)


	DECLARE @SALES_FROM_DATE DATETIME
	DECLARE @SALES_TO_DATE DATETIME
	DECLARE @TEMP_DATE VARCHAR(20)	

	INSERT INTO @PROCESS_STEPS_TABLE( ProcessStep_ID ) SELECT ProcessStep_ID FROM FN_GET_PROCESS_STEPS_IN_STRING( @PRODUCT_IDS )

	DECLARE @RESULTS_TABLE TABLE ( BaseProductName VARCHAR(50),
					BaseProductID VARCHAR(20),
					ProcessStepID INT,
					ProductID INT,
					Product VARCHAR(50),
					PS_Type VARCHAR(50),
					Product_Type VARCHAR(50),
					Product_Type_ID INT,
					Req_Sch_Date DATETIME,
					Shipped_Qty BIGINT, 
					Unit_Cost FLOAT,
					Amount FLOAT,
					ShipmentMonth INT, 
					ShipmentYear INT, 
					QuarterStart INT, 
					QuarterEnd INT, 
					ActYear INT)	


	DECLARE @SEARCH_RESULTS_TABLE TABLE ( BaseProductName VARCHAR(50),
					BaseProductID VARCHAR(20),
					ProcessStepID INT,
					ProductID INT,
					Product VARCHAR(50),
					PS_Type VARCHAR(50),
					Product_Type VARCHAR(50),
					Product_Type_ID INT,
					Req_Sch_Date DATETIME,
					Shipped_Qty BIGINT, 
					Unit_Cost FLOAT,
					Amount FLOAT,
					ShipmentMonth INT, 
					ShipmentYear INT, 
					QuarterStart INT, 
					QuarterEnd INT, 
					ActYear INT)	

	DECLARE @LINEITEM_ALL_PRODUCTS_TABLE TABLE ( BaseProductName VARCHAR(50),
					BaseProductID VARCHAR(20),
					ProcessStepID INT,
					ProductID INT,
					Product VARCHAR(50),
					PS_Type VARCHAR(50),
					Product_Type VARCHAR(50),
					Product_Type_ID INT,
					Req_Sch_Date DATETIME,
					Shipped_Qty BIGINT, 
					Unit_Cost FLOAT,
					Amount FLOAT,
					ShipmentMonth INT, 
					ShipmentYear INT, 
					QuarterStart INT, 
					QuarterEnd INT, 
					ActYear INT)	

	DECLARE @PARENT_PRODUCT_CURSOR CURSOR
	DECLARE @LINE_ITEM_CURSOR CURSOR
	DECLARE @LOT_NUMBER_CURSOR CURSOR


	SET @RESULT_BASEPRODUCTNAME  	= NULL	
	SET @RESULT_PROCESS_STEP_ID 	= NULL
	SET @RESULT_PRODUCT_ID 		= NULL
	SET @RESULT_PRODUCT  		= NULL

	/*
	LOAD ALL PRODUCT WHICH INCLUDE ALL CHILD CHIP + ALL MOTHER AND CHILD BOARDS AND KITS	
	*/
	IF @PRODUCT_IDS <> '0'
	BEGIN
		INSERT INTO @SEARCH_RESULTS_TABLE 
		SELECT 
		( 
		SELECT PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = 
		CASE 
			WHEN P.P_PARENT_PRODUCT_ID = -1 THEN P.PRODUCT_ID 
			WHEN P.P_PARENT_PRODUCT_ID <> -1 THEN P.P_PARENT_PRODUCT_ID	
		END 
		) AS BASEPRODUCTNAME, 
		CASE 
			WHEN P.P_PARENT_PRODUCT_ID = -1 THEN P.PRODUCT_ID
			WHEN P.P_PARENT_PRODUCT_ID <> -1 THEN P.P_PARENT_PRODUCT_ID
		END ,
		P.PROCESSSTEP_ID, P.PRODUCT_ID, PART_NUMBER, PS_TYPE,
		CASE 
			WHEN P_TYPE IS NULL THEN 'CHIP'
			WHEN P_TYPE LIKE LTRIM(RTRIM('BOARD')) THEN 'BOARD'
			WHEN P_TYPE LIKE LTRIM(RTRIM('KIT')) THEN 'KIT'
		END AS PRODUCT_TYPE,
		CASE 
			WHEN P_TYPE IS NULL THEN 1
			WHEN P_TYPE LIKE LTRIM(RTRIM('BOARD')) THEN 2
			WHEN P_TYPE LIKE LTRIM(RTRIM('KIT')) THEN 3
		END AS PRODUCT_TYPE_ID, NULL, 0, 0, 0, 0, 0, 0, 0, 0
		FROM PRODUCT P, PROCESS_STEP 
		WHERE P.PROCESSSTEP_ID = PROCESS_STEP.PROCESSSTEP_ID
		AND ( P_PARENT_PRODUCT_ID IN (SELECT ProcessStep_ID FROM @PROCESS_STEPS_TABLE) OR P.PRODUCT_ID = (SELECT ProcessStep_ID FROM @PROCESS_STEPS_TABLE) )
		AND P_PARENT_PRODUCT_ID IS NOT NULL 
		AND PS_TYPE_NAME = 'FINISHED GOODS'
		ORDER BY PRODUCT_TYPE_ID, BaseProductName 
	END
	ELSE
	BEGIN
		INSERT INTO @SEARCH_RESULTS_TABLE 
		SELECT 
		( 
		SELECT PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = 
		CASE 
			WHEN P.P_PARENT_PRODUCT_ID = -1 THEN P.PRODUCT_ID 
			WHEN P.P_PARENT_PRODUCT_ID <> -1 THEN P.P_PARENT_PRODUCT_ID	
		END
		) AS BASEPRODUCTNAME, 
		CASE 
			WHEN P.P_PARENT_PRODUCT_ID = -1 THEN P.PRODUCT_ID
			WHEN P.P_PARENT_PRODUCT_ID <> -1 THEN P.P_PARENT_PRODUCT_ID
		END ,
		P.PROCESSSTEP_ID, P.PRODUCT_ID, PART_NUMBER, PS_TYPE,
		CASE 
			WHEN P_TYPE IS NULL THEN 'CHIP'
			WHEN P_TYPE LIKE LTRIM(RTRIM('BOARD')) THEN 'BOARD'
			WHEN P_TYPE LIKE LTRIM(RTRIM('KIT')) THEN 'KIT'
		END AS PRODUCT_TYPE,
		CASE 
			WHEN P_TYPE IS NULL THEN 1
			WHEN P_TYPE LIKE LTRIM(RTRIM('BOARD')) THEN 2
			WHEN P_TYPE LIKE LTRIM(RTRIM('KIT')) THEN 3
		END AS PRODUCT_TYPE_ID, NULL, 0, 0, 0, 0, 0, 0, 0, 0
		FROM PRODUCT P, PROCESS_STEP 
		WHERE P.PROCESSSTEP_ID = PROCESS_STEP.PROCESSSTEP_ID
		AND P_PARENT_PRODUCT_ID IS NOT NULL 
		AND PS_TYPE_NAME = 'FINISHED GOODS' AND ( P_TYPE IS NULL OR P_TYPE = 'BOARD' OR P_TYPE = 'KIT')
		ORDER BY PRODUCT_TYPE_ID, BaseProductName 
	END

	INSERT INTO @LINEITEM_ALL_PRODUCTS_TABLE
	SELECT 
	( 
	SELECT PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = 
	CASE 
		WHEN P.P_PARENT_PRODUCT_ID = -1 THEN P.PRODUCT_ID 
		WHEN P.P_PARENT_PRODUCT_ID <> -1 THEN P.P_PARENT_PRODUCT_ID	
	END 
	) AS BASEPRODUCTNAME, 
	CASE 
		WHEN P.P_PARENT_PRODUCT_ID = -1 THEN P.PRODUCT_ID
		WHEN P.P_PARENT_PRODUCT_ID <> -1 THEN P.P_PARENT_PRODUCT_ID
	END ,
	P.PROCESSSTEP_ID, P.PRODUCT_ID, PART_NUMBER, PS_TYPE,
	CASE 
		WHEN P_TYPE IS NULL THEN 'CHIP'
		WHEN P_TYPE LIKE LTRIM(RTRIM('BOARD')) THEN 'BOARD'
		WHEN P_TYPE LIKE LTRIM(RTRIM('KIT')) THEN 'KIT'
	END AS PRODUCT_TYPE,
	CASE 
		WHEN P_TYPE IS NULL THEN 1
		WHEN P_TYPE LIKE LTRIM(RTRIM('BOARD')) THEN 2
		WHEN P_TYPE LIKE LTRIM(RTRIM('KIT')) THEN 3
	END AS PRODUCT_TYPE_ID, NULL, 0, 0, 0, 0, 0, 0, 0, 0
	FROM PRODUCT P, PROCESS_STEP 
	WHERE P.PROCESSSTEP_ID = PROCESS_STEP.PROCESSSTEP_ID AND P.PRODUCT_ID IN (SELECT DISTINCT Product_ID FROM Customer_Open_Order_LineItem)

	IF @PRODUCT_IDS <> '0'
	BEGIN
		DELETE FROM @LINEITEM_ALL_PRODUCTS_TABLE WHERE BaseProductID <> @PRODUCT_IDS
	END

	INSERT INTO @SEARCH_RESULTS_TABLE SELECT * FROM @LINEITEM_ALL_PRODUCTS_TABLE WHERE ProductID NOT IN ( SELECT ProductID FROM @SEARCH_RESULTS_TABLE )

	SET @PARENT_PRODUCT_CURSOR = CURSOR FOR 
	SELECT BaseProductName, BaseProductID, ProcessStepID, ProductID, Product, PS_Type, Product_Type, Product_Type_ID FROM @SEARCH_RESULTS_TABLE
	ORDER BY Product_Type_ID, BaseProductName, ProcessStepID, ProductID


	OPEN @PARENT_PRODUCT_CURSOR 
	FETCH NEXT FROM @PARENT_PRODUCT_CURSOR INTO @RESULT_BASEPRODUCTNAME, @RESULT_BASE_PRODUCT_ID, @RESULT_PROCESS_STEP_ID, @RESULT_PRODUCT_ID, @RESULT_PRODUCT,
	@RESULT_PS_TYPE, @RESULT_PRODUCT_TYPE, @RESULT_PRODUCT_TYPE_ID
	WHILE @@FETCH_STATUS = 0
	BEGIN

		--PRINT 'START-################################'
		--PRINT ' @RESULT_PRODUCT_ID = ' + CONVERT(VARCHAR,@RESULT_PRODUCT_ID) + ' @RESULT_PRODUCT = ' + @RESULT_PRODUCT 	

		SET @TEMP_CSLIT_ID		= NULL
		SET @TEMP_LINEITEM_ID		= NULL
		SET @TEMP_PRODUCT_ID		= NULL
		SET @TEMP_PS_ID			= NULL
		SET @TEMP_UNIT_COST		= NULL
		SET @TEMP_SHIPPED_QTY		= NULL
		SET @TEMP_SHIPPED_DATE		= NULL
		SET @TEMP_SHIPPED_PART_NO	= NULL

		SET @SHIP_REQ_SCH_DT = NULL
		SET @SHIP_YR_I = NULL
		SET @SHIP_MT_I = NULL
		SET @SHIP_QT_I = NULL
		SET @SHIP_QT_END_I = NULL
		SET @ACT_YEAR = NULL

		/*
		TO LOAD SHIPPED QTY FROM CSLIT TABLE FOR EACH PRODUCT
		*/
		IF ( @CURRENT_QUARTER = 'ALL' )
		BEGIN
			SET @LINE_ITEM_CURSOR = CURSOR FOR SELECT CSLIT.CSLIT_ID, COOL.LineItem_ID, Product.Product_ID, COOL.COOL_Unit_Cost, CSLIT.CSLIT_Qty_Shipped, CST.CST_Ship_Date, ISNULL(CSLIT.CSLIT_Shipped_Part_Number, '') --AS CSLIT_Shipped_Part_Number 
			FROM Customer_Shipping_Table CST 
			INNER JOIN Customer_Shipping_Line_Item_Table CSLIT ON CST.CustomerShipping_ID = CSLIT.CustomerShipping_ID 
			INNER JOIN Customer_Open_Order_LineItem COOL ON CSLIT.LineItem_ID = COOL.LineItem_ID 
			INNER JOIN Sales_Order SO ON COOL.SalesOrder_ID = SO.SalesOrder_ID 
			INNER JOIN Product ON COOL.Product_ID = Product.Product_ID
			WHERE SO.SO_Is_Sample = 1 AND CST.CST_STATUS = 'SHIPPED' AND COOL.Product_ID = @RESULT_PRODUCT_ID AND YEAR( CST.CST_Ship_Date ) = @CURRENT_YEAR
		END
		ELSE
		BEGIN
			SET @LINE_ITEM_CURSOR = CURSOR FOR SELECT CSLIT.CSLIT_ID, COOL.LineItem_ID, Product.Product_ID, COOL.COOL_Unit_Cost, CSLIT.CSLIT_Qty_Shipped, CST.CST_Ship_Date, ISNULL(CSLIT.CSLIT_Shipped_Part_Number, '') --AS CSLIT_Shipped_Part_Number 
			FROM Customer_Shipping_Table CST 
			INNER JOIN Customer_Shipping_Line_Item_Table CSLIT ON CST.CustomerShipping_ID = CSLIT.CustomerShipping_ID 
			INNER JOIN Customer_Open_Order_LineItem COOL ON CSLIT.LineItem_ID = COOL.LineItem_ID 
			INNER JOIN Sales_Order SO ON COOL.SalesOrder_ID = SO.SalesOrder_ID 
			INNER JOIN Product ON COOL.Product_ID = Product.Product_ID
			WHERE SO.SO_Is_Sample = 1 AND CST.CST_STATUS = 'SHIPPED' AND COOL.Product_ID = @RESULT_PRODUCT_ID AND YEAR( CST.CST_Ship_Date ) = @CURRENT_YEAR AND DATEPART(Q, CST.CST_Ship_Date ) = @CURRENT_QUARTER 
		END
		
		OPEN @LINE_ITEM_CURSOR 
		FETCH NEXT FROM @LINE_ITEM_CURSOR INTO @TEMP_CSLIT_ID, @TEMP_LINEITEM_ID, @TEMP_PRODUCT_ID, @TEMP_UNIT_COST, @TEMP_SHIPPED_QTY, @TEMP_SHIPPED_DATE, @TEMP_SHIPPED_PART_NO

		WHILE @@FETCH_STATUS = 0
		BEGIN
			SET @PRODUCT_PARENT_PRODUCT_ID	= NULL
			
			
			--PRINT ' @TEMP_CSLIT_ID = ' + CONVERT(VARCHAR,@TEMP_CSLIT_ID) + ' @TEMP_SHIPPED_PART_NO = ' + CONVERT(VARCHAR,@TEMP_SHIPPED_PART_NO) + ' @TEMP_SHIPPED_QTY = ' + CONVERT(VARCHAR,@TEMP_SHIPPED_QTY) 					
			

			SET @SHIP_REQ_SCH_DT = @TEMP_SHIPPED_DATE

			SET @SHIP_YR_I = DATEPART(YYYY, @SHIP_REQ_SCH_DT)
			SET @SHIP_MT_I = DATEPART(MM, @SHIP_REQ_SCH_DT)
			SET @ACT_YEAR = @SHIP_YR_I 
			
			IF (@SHIP_MT_I >=1 AND @SHIP_MT_I <=3)
			BEGIN
				SET @SHIP_QT_I = 1
				SET @SHIP_QT_END_I = 3
			END
			ELSE IF (@SHIP_MT_I >=4 AND @SHIP_MT_I <=6)
			BEGIN
				SET @SHIP_QT_I = 2
				SET @SHIP_QT_END_I = 6 
			END
			ELSE IF (@SHIP_MT_I >=7 AND @SHIP_MT_I <=9)
			BEGIN
				SET @SHIP_QT_I = 3
				SET @SHIP_QT_END_I = 9
			END						
			ELSE IF (@SHIP_MT_I >=10 AND @SHIP_MT_I <=12)
			BEGIN
				SET @SHIP_QT_I = 4
				SET @SHIP_QT_END_I = 12
			END
			
			--TO GET PARENT PRODUCT ID FOR GIVEN PRODUCT
			SELECT @PRODUCT_PARENT_PRODUCT_ID = CASE WHEN P_Parent_Product_ID <> -1 THEN P_Parent_Product_ID WHEN P_Parent_Product_ID = -1 THEN Product_ID END 
			FROM PRODUCT WHERE PRODUCT_ID = @TEMP_PRODUCT_ID				

			--TO GET PRODUCT TYPE ( CHIP OR BOARD OR KIT ) FOR GIVEN PRODUCT
			SELECT @CHK_PRODUCT_FOR_BOARD_KIT_OR_CHIP = CASE WHEN P_TYPE IS NULL THEN 'CHIP' WHEN P_TYPE LIKE 'BOARD' THEN 'BOARD' WHEN P_TYPE LIKE 'KIT' THEN 'KIT' END 
			FROM PRODUCT WHERE PRODUCT_ID = @RESULT_PRODUCT_ID
			/*
			IF PRODUCT TYPE IS CHIP OR NO SUBSTITUTE IS NOT PRESENT FOR THIS LINE ITEM INSERT DIRECTLY INTO RESULTS TABLE
			IF PRODUCT TYPE IS BOARD OR KIT AND SUBSTITUTION IS PRESENT THAN ITERATE THROUGH CSLNT TABLE FOR GETTING THE ORIGINAL PRODUCT SHIPPED
			*/
			IF( @TEMP_SHIPPED_PART_NO = '' OR @CHK_PRODUCT_FOR_BOARD_KIT_OR_CHIP = 'CHIP'  )
			BEGIN
				INSERT INTO @RESULTS_TABLE VALUES( @RESULT_BASEPRODUCTNAME, @RESULT_BASE_PRODUCT_ID, @RESULT_PROCESS_STEP_ID, @RESULT_PRODUCT_ID, @RESULT_PRODUCT,
				@RESULT_PS_TYPE, @RESULT_PRODUCT_TYPE, @RESULT_PRODUCT_TYPE_ID, @SHIP_REQ_SCH_DT, @TEMP_SHIPPED_QTY, @TEMP_UNIT_COST, 
				(  @TEMP_SHIPPED_QTY * @TEMP_UNIT_COST ), @SHIP_MT_I, @SHIP_YR_I, @SHIP_QT_I, @SHIP_QT_END_I, @SHIP_YR_I )
			END
			ELSE
			BEGIN
				SET @LOT_ID			= NULL
				SET @LOT_PRODUCT_ID		= NULL
				SET @LOT_PARENT_PRODUCT_ID 	= NULL
				SET @LOT_SHIPPED_QTY 		= NULL
				SET @LOT_BASEPRODUCTNAME  = NULL
				SET @LOT_BASE_PRODUCT_ID  = NULL
				SET @LOT_PROCESS_STEP_ID  = NULL
				SET @TEMP_LOT_PRODUCT_ID  = NULL
				SET @LOT_PRODUCT = NULL
				SET @LOT_PS_TYPE = NULL
				SET @LOT_PRODUCT_TYPE = NULL
				SET @LOT_PRODUCT_TYPE_ID = NULL
				/*
				TO LOAD LOTS SHIPPED QTY FROM CSLNT TABLE FOR EACH LINE ITEM FOR GIVEN PRODUCT
				*/
				SET @LOT_NUMBER_CURSOR = CURSOR FOR SELECT CSLNT.Lot_ID, P.Product_ID, CSLNT.CSLNT_Qty_Pull  FROM Customer_Shipping_Line_Item_Table CSLIT 
				INNER JOIN Customer_Shipping_Lot_Number_Table CSLNT ON CSLIT.CSLIT_ID = CSLNT.CSLIT_ID 
				INNER JOIN Lot_Table LT ON CSLNT.Lot_ID = LT.Lot_ID 
				INNER JOIN Product P ON LT.ProcessStep_ID = P.ProcessStep_ID
				WHERE CSLNT.CSLIT_ID = @TEMP_CSLIT_ID
					
				OPEN @LOT_NUMBER_CURSOR 
				FETCH NEXT FROM @LOT_NUMBER_CURSOR INTO @LOT_ID, @LOT_PRODUCT_ID, @LOT_SHIPPED_QTY 
				WHILE @@FETCH_STATUS = 0
				BEGIN
					--PRINT '1 @LOT_ID = ' + CONVERT(VARCHAR,@LOT_ID ) + ' @LOT_PRODUCT_ID = ' + CONVERT(VARCHAR,@LOT_PRODUCT_ID) + ' @LOT_SHIPPED_QTY = ' + CONVERT(VARCHAR,@LOT_SHIPPED_QTY)
					--PRINT ' @TEMP_CSLIT_ID = ' + CONVERT(VARCHAR,@TEMP_CSLIT_ID) 

					SELECT @LOT_PARENT_PRODUCT_ID = CASE WHEN P_Parent_Product_ID <> -1 THEN P_Parent_Product_ID WHEN P_Parent_Product_ID = -1 THEN Product_ID END 
					FROM PRODUCT WHERE PRODUCT_ID = @LOT_PRODUCT_ID
					
					/*
					IF LINE ITEM PARENT PRODUCT AND PULLED LOT PRODUCT ARE SAME THEN INSERT DIRECTLY INTO RESULTS TABLE
					ELSE INSERT INTO RESULTS TABLE WITH PULLED LOT PRODUCT DETAILS
					*/
					IF( @LOT_PARENT_PRODUCT_ID = @PRODUCT_PARENT_PRODUCT_ID )
					BEGIN
						--PRINT '0000000000000000000'
						INSERT INTO @RESULTS_TABLE VALUES( @RESULT_BASEPRODUCTNAME, @RESULT_BASE_PRODUCT_ID, @RESULT_PROCESS_STEP_ID, @RESULT_PRODUCT_ID, @RESULT_PRODUCT,
						@RESULT_PS_TYPE, @RESULT_PRODUCT_TYPE, @RESULT_PRODUCT_TYPE_ID, @SHIP_REQ_SCH_DT, @LOT_SHIPPED_QTY, @TEMP_UNIT_COST, 
						(  @LOT_SHIPPED_QTY * @TEMP_UNIT_COST ), @SHIP_MT_I, @SHIP_YR_I, @SHIP_QT_I, @SHIP_QT_END_I, @SHIP_YR_I )
					END
					ELSE
					BEGIN
						--PRINT '11111111111111111'
						SELECT @LOT_BASEPRODUCTNAME = BaseProductName, @LOT_BASE_PRODUCT_ID = BaseProductID , @LOT_PROCESS_STEP_ID = ProcessStepID, @TEMP_LOT_PRODUCT_ID = ProductID, @LOT_PRODUCT = Product,
						@LOT_PS_TYPE = PS_Type, @LOT_PRODUCT_TYPE = Product_Type, @LOT_PRODUCT_TYPE_ID = Product_Type_ID
						FROM  @SEARCH_RESULTS_TABLE WHERE ProductID = ( SELECT P.Product_ID FROM Product P INNER JOIN Lot_Table LT ON P.ProcessStep_ID = LT.ProcessStep_ID WHERE LT.Lot_ID = @LOT_ID )
						
						IF @TEMP_LOT_PRODUCT_ID IS NOT NULL 
						BEGIN
							INSERT INTO @RESULTS_TABLE VALUES( @LOT_BASEPRODUCTNAME, @LOT_BASE_PRODUCT_ID, @LOT_PROCESS_STEP_ID, @TEMP_LOT_PRODUCT_ID, @LOT_PRODUCT,
							@LOT_PS_TYPE, @LOT_PRODUCT_TYPE, @LOT_PRODUCT_TYPE_ID, @SHIP_REQ_SCH_DT, @LOT_SHIPPED_QTY, @TEMP_UNIT_COST, 
							(  @LOT_SHIPPED_QTY * @TEMP_UNIT_COST ), @SHIP_MT_I, @SHIP_YR_I, @SHIP_QT_I, @SHIP_QT_END_I, @SHIP_YR_I )
							--PRINT '222222222222222222'
						END
					END					

					SET @LOT_ID			= NULL
					SET @LOT_PRODUCT_ID		= NULL
					SET @LOT_PARENT_PRODUCT_ID 	= NULL
					SET @LOT_SHIPPED_QTY 		= NULL
					SET @LOT_BASEPRODUCTNAME  = NULL
					SET @LOT_BASE_PRODUCT_ID  = NULL
					SET @LOT_PROCESS_STEP_ID  = NULL
					SET @TEMP_LOT_PRODUCT_ID  = NULL
					SET @LOT_PRODUCT = NULL
					SET @LOT_PS_TYPE = NULL
					SET @LOT_PRODUCT_TYPE = NULL
					SET @LOT_PRODUCT_TYPE_ID = NULL

					FETCH NEXT FROM @LOT_NUMBER_CURSOR INTO @LOT_ID, @LOT_PRODUCT_ID, @LOT_SHIPPED_QTY 
				END
				CLOSE @LOT_NUMBER_CURSOR
				DEALLOCATE @LOT_NUMBER_CURSOR

			END
			--PRINT 'END-################################'
			SET @TEMP_SHIPPED_QTY = NULL
			SET @TEMP_UNIT_COST = NULL
	
			SET @SHIP_REQ_SCH_DT = NULL
			SET @SHIP_YR_I = NULL
			SET @SHIP_MT_I = NULL
			SET @SHIP_QT_I = NULL
			SET @SHIP_QT_END_I = NULL
			SET @ACT_YEAR = NULL

			SET @TEMP_CSLIT_ID		= NULL
			SET @TEMP_LINEITEM_ID		= NULL
			SET @TEMP_PRODUCT_ID		= NULL
			SET @TEMP_PS_ID			= NULL
			SET @TEMP_UNIT_COST		= NULL
			SET @TEMP_SHIPPED_QTY		= NULL
			SET @TEMP_SHIPPED_DATE		= NULL
			SET @TEMP_SHIPPED_PART_NO	= NULL
			SET @PRODUCT_PARENT_PRODUCT_ID	= NULL
			
			FETCH NEXT FROM @LINE_ITEM_CURSOR INTO @TEMP_CSLIT_ID, @TEMP_LINEITEM_ID, @TEMP_PRODUCT_ID, @TEMP_UNIT_COST, @TEMP_SHIPPED_QTY, @TEMP_SHIPPED_DATE, @TEMP_SHIPPED_PART_NO
		END
		CLOSE @LINE_ITEM_CURSOR
		DEALLOCATE @LINE_ITEM_CURSOR

		/*
		GETTING SUBSTITUTABLE PRODUCT OF TYPE MOTHER BOARDS OR KITS SHIPPED PULLED QTY.
		*/	
		
		IF ( @PRODUCT_IDS <> '0' AND @RESULT_BASE_PRODUCT_ID = @RESULT_PRODUCT_ID)
		BEGIN
			SET @LOT_PRODUCT_ID 		= NULL
			SET @LOT_SHIPPED_DATE 		= NULL
			SET @LOT_SHIPPED_QTY 		= NULL
			SET @LINEITEM_PRODUCT_ID 	= NULL
			SET @LINEITEM_PRODUCT_PARENT_PRODUCT_ID = NULL
			SET @TEMP_UNIT_COST		= NULL
			
			IF ( @CURRENT_QUARTER = 'ALL' )
			BEGIN
				
				SET @LOT_NUMBER_CURSOR = CURSOR FOR SELECT P.Product_ID, CST.CST_Ship_Date, CSLNT.CSLNT_Qty_Pull,
				(SELECT P.Product_ID FROM Customer_Open_Order_LineItem COOL INNER JOIN Product P ON COOL.Product_ID = P.Product_ID WHERE COOL.LineItem_ID = CSLIT.LineItem_ID) AS LineItem_ProductID,
				COOL.COOL_Unit_Cost
				FROM Customer_Shipping_Lot_Number_Table CSLNT 
				INNER JOIN Customer_Shipping_Line_Item_Table CSLIT ON CSLNT.CSLIT_ID = CSLIT.CSLIT_ID 
				INNER JOIN Customer_Shipping_Table CST ON CSLIT.CustomerShipping_ID = CST.CustomerShipping_ID 
				INNER JOIN Sales_Order SO ON CST.SalesOrder_ID = SO.SalesOrder_ID 
				INNER JOIN Lot_Table LT ON CSLNT.Lot_ID = LT.Lot_ID 
				INNER JOIN Product P ON LT.ProcessStep_ID = P.ProcessStep_ID
				INNER JOIN Customer_Open_Order_LineItem COOL ON CSLIT.LineItem_ID = COOL.LineItem_ID
				WHERE (SO.SO_Is_Sample = 1) AND (CST.CST_Status = 'SHIPPED') AND (CSLNT.CSLNT_Qty_Pull > 0) AND P.Product_ID = @RESULT_PRODUCT_ID AND P.P_Type IS NOT NULL AND YEAR( CST.CST_Ship_Date ) = @CURRENT_YEAR


			END
			ELSE
			BEGIN
				SET @LOT_NUMBER_CURSOR = CURSOR FOR SELECT P.Product_ID, CST.CST_Ship_Date, CSLNT.CSLNT_Qty_Pull,
				(SELECT P.Product_ID FROM Customer_Open_Order_LineItem COOL INNER JOIN Product P ON COOL.Product_ID = P.Product_ID WHERE COOL.LineItem_ID = CSLIT.LineItem_ID) AS LineItem_ProductID,
				COOL.COOL_Unit_Cost
				FROM Customer_Shipping_Lot_Number_Table CSLNT 
				INNER JOIN Customer_Shipping_Line_Item_Table CSLIT ON CSLNT.CSLIT_ID = CSLIT.CSLIT_ID 
				INNER JOIN Customer_Shipping_Table CST ON CSLIT.CustomerShipping_ID = CST.CustomerShipping_ID 
				INNER JOIN Sales_Order SO ON CST.SalesOrder_ID = SO.SalesOrder_ID 
				INNER JOIN Lot_Table LT ON CSLNT.Lot_ID = LT.Lot_ID 
				INNER JOIN Product P ON LT.ProcessStep_ID = P.ProcessStep_ID
				INNER JOIN Customer_Open_Order_LineItem COOL ON CSLIT.LineItem_ID = COOL.LineItem_ID
				WHERE (SO.SO_Is_Sample = 1) AND (CST.CST_Status = 'SHIPPED') AND (CSLNT.CSLNT_Qty_Pull > 0) AND P.Product_ID = @RESULT_PRODUCT_ID AND P.P_Type IS NOT NULL 
				AND YEAR( CST.CST_Ship_Date ) = @CURRENT_YEAR AND DATEPART(Q, CST.CST_Ship_Date ) = @CURRENT_QUARTER 

			END
				
			OPEN @LOT_NUMBER_CURSOR 
			FETCH NEXT FROM @LOT_NUMBER_CURSOR INTO @LOT_PRODUCT_ID, @LOT_SHIPPED_DATE, @LOT_SHIPPED_QTY, @LINEITEM_PRODUCT_ID, @TEMP_UNIT_COST 
			WHILE @@FETCH_STATUS = 0
			BEGIN
				--PRINT '1 @LOT_ID = ' + CONVERT(VARCHAR,@LOT_ID ) + ' @LOT_PRODUCT_ID = ' + CONVERT(VARCHAR,@LOT_PRODUCT_ID) + ' @LOT_SHIPPED_QTY = ' + CONVERT(VARCHAR,@LOT_SHIPPED_QTY)
				--PRINT ' @TEMP_CSLIT_ID = ' + CONVERT(VARCHAR,@TEMP_CSLIT_ID) 

				SET @SHIP_REQ_SCH_DT = @LOT_SHIPPED_DATE

				SET @SHIP_YR_I = DATEPART(YYYY, @SHIP_REQ_SCH_DT)
				SET @SHIP_MT_I = DATEPART(MM, @SHIP_REQ_SCH_DT)
				SET @ACT_YEAR = @SHIP_YR_I 
				
				IF (@SHIP_MT_I >=1 AND @SHIP_MT_I <=3)
				BEGIN
					SET @SHIP_QT_I = 1
					SET @SHIP_QT_END_I = 3
				END
				ELSE IF (@SHIP_MT_I >=4 AND @SHIP_MT_I <=6)
				BEGIN
					SET @SHIP_QT_I = 2
					SET @SHIP_QT_END_I = 6 
				END
				ELSE IF (@SHIP_MT_I >=7 AND @SHIP_MT_I <=9)
				BEGIN
					SET @SHIP_QT_I = 3
					SET @SHIP_QT_END_I = 9
				END						
				ELSE IF (@SHIP_MT_I >=10 AND @SHIP_MT_I <=12)
				BEGIN
					SET @SHIP_QT_I = 4
					SET @SHIP_QT_END_I = 12
				END

				SELECT @LINEITEM_PRODUCT_PARENT_PRODUCT_ID = CASE WHEN P_Parent_Product_ID <> -1 THEN P_Parent_Product_ID WHEN P_Parent_Product_ID = -1 THEN Product_ID END 
				FROM PRODUCT WHERE PRODUCT_ID = @LINEITEM_PRODUCT_ID

				--PRINT '1@LINEITEM_PRODUCT_PARENT_PRODUCT_ID = ' + CONVERT(VARCHAR,@LINEITEM_PRODUCT_PARENT_PRODUCT_ID) + ' @LOT_PRODUCT_ID = ' + CONVERT(VARCHAR,@LOT_PRODUCT_ID ) + ' @LINEITEM_PRODUCT_ID = ' + CONVERT(VARCHAR,@LINEITEM_PRODUCT_ID) + ' @LOT_SHIPPED_QTY = ' + CONVERT(VARCHAR,@LOT_SHIPPED_QTY)
				--PRINT ' @TEMP_CSLIT_ID = ' + CONVERT(VARCHAR,@TEMP_CSLIT_ID) 
				SELECT @RESULT_PRODUCT = ISNULL( PART_NUMBER, @RESULT_PRODUCT ) FROM PRODUCT WHERE PRODUCT_ID = ( SELECT PRODUCT_ID FROM CUSTOMER_OPEN_ORDER_LINEITEM WHERE LineItem_ID = @LINEITEM_PRODUCT_ID )
				
				IF( @LINEITEM_PRODUCT_PARENT_PRODUCT_ID <> @LOT_PRODUCT_ID )
				BEGIN
					--PRINT  'INSERT SUBSTITUTABLES @LOT_PRODUCT_ID = ' + CONVERT(VARCHAR,@LOT_PRODUCT_ID)
					INSERT INTO @RESULTS_TABLE VALUES( @RESULT_BASEPRODUCTNAME, @RESULT_BASE_PRODUCT_ID, @RESULT_PROCESS_STEP_ID, @RESULT_PRODUCT_ID, @RESULT_PRODUCT,
					@RESULT_PS_TYPE, @RESULT_PRODUCT_TYPE, @RESULT_PRODUCT_TYPE_ID, @SHIP_REQ_SCH_DT, @LOT_SHIPPED_QTY, @TEMP_UNIT_COST, 
					(  @LOT_SHIPPED_QTY * @TEMP_UNIT_COST ), @SHIP_MT_I, @SHIP_YR_I, @SHIP_QT_I, @SHIP_QT_END_I, @SHIP_YR_I )
				END
				--PRINT '2@LINEITEM_PRODUCT_PARENT_PRODUCT_ID = ' + CONVERT(VARCHAR,@LINEITEM_PRODUCT_PARENT_PRODUCT_ID) + ' @LOT_PRODUCT_ID = ' + CONVERT(VARCHAR,@LOT_PRODUCT_ID ) + ' @LINEITEM_PRODUCT_ID = ' + CONVERT(VARCHAR,@LINEITEM_PRODUCT_ID) + ' @LOT_SHIPPED_QTY = ' + CONVERT(VARCHAR,@LOT_SHIPPED_QTY)
				SET @LOT_PRODUCT_ID 		= NULL
				SET @LOT_SHIPPED_DATE 		= NULL
				SET @LOT_SHIPPED_QTY 		= NULL
				SET @LINEITEM_PRODUCT_ID 	= NULL
				SET @LINEITEM_PRODUCT_PARENT_PRODUCT_ID = NULL
				SET @TEMP_UNIT_COST		= NULL
				SET @SHIP_REQ_SCH_DT 		= NULL
				SET @SHIP_YR_I 			= NULL
				SET @SHIP_MT_I 			= NULL
				SET @SHIP_QT_I 			= NULL
				SET @SHIP_QT_END_I 		= NULL
				SET @ACT_YEAR 			= NULL

				FETCH NEXT FROM @LOT_NUMBER_CURSOR INTO @LOT_PRODUCT_ID, @LOT_SHIPPED_DATE, @LOT_SHIPPED_QTY, @LINEITEM_PRODUCT_ID, @TEMP_UNIT_COST 
			END
			CLOSE @LOT_NUMBER_CURSOR
			DEALLOCATE @LOT_NUMBER_CURSOR
		END
		

		SET @RESULT_BASEPRODUCTNAME = NULL
		SET @RESULT_BASE_PRODUCT_ID = NULL
		SET @RESULT_PROCESS_STEP_ID = NULL
		SET @RESULT_PRODUCT_ID = NULL
		SET @RESULT_PRODUCT = NULL
		SET @RESULT_PS_TYPE = NULL
		SET @RESULT_PRODUCT_TYPE = NULL
		SET @RESULT_PRODUCT_TYPE_ID = NULL

		FETCH NEXT FROM @PARENT_PRODUCT_CURSOR INTO @RESULT_BASEPRODUCTNAME, @RESULT_BASE_PRODUCT_ID, @RESULT_PROCESS_STEP_ID, @RESULT_PRODUCT_ID, @RESULT_PRODUCT,
		@RESULT_PS_TYPE, @RESULT_PRODUCT_TYPE, @RESULT_PRODUCT_TYPE_ID
	END
	CLOSE @PARENT_PRODUCT_CURSOR
	DEALLOCATE @PARENT_PRODUCT_CURSOR

	SET @SHIP_REQ_SCH_DT = NULL
	SET @SHIP_YR_I = NULL
	SET @SHIP_MT_I = NULL
	SET @SHIP_QT_I = NULL
	SET @SHIP_QT_END_I = NULL
	SET @ACT_YEAR = NULL
	SET @SHIP_REQ_SCH_DT = NULL

	SET @SHIP_YR_I = @CURRENT_YEAR
	SET @ACT_YEAR = @CURRENT_YEAR 

	IF ( @CURRENT_QUARTER = 'ALL' OR @CURRENT_QUARTER = '1' )
	BEGIN
		SET @SHIP_MT_I = 1
		SET @SHIP_QT_I = 1
		SET @SHIP_QT_END_I = 3
		SET @SHIP_REQ_SCH_DT = CONVERT( DATETIME, '01/01/' + CONVERT(VARCHAR,@SHIP_YR_I) )
	END
	ELSE
	BEGIN
		IF ( @CURRENT_QUARTER = '2' )
		BEGIN
			SET @SHIP_MT_I = 4
			SET @SHIP_QT_I = 2
			SET @SHIP_QT_END_I = 6
			SET @SHIP_REQ_SCH_DT = CONVERT( DATETIME, '04/01/' +  CONVERT(VARCHAR,@SHIP_YR_I) )
		END
		ELSE IF ( @CURRENT_QUARTER = '3' )
		BEGIN
			SET @SHIP_MT_I = 7
			SET @SHIP_QT_I = 3
			SET @SHIP_QT_END_I = 9
			SET @SHIP_REQ_SCH_DT = CONVERT( DATETIME, '07/01/' +  CONVERT(VARCHAR,@SHIP_YR_I) )
		END
		ELSE IF ( @CURRENT_QUARTER = '4' )
		BEGIN
			SET @SHIP_MT_I = 10
			SET @SHIP_QT_I = 4
			SET @SHIP_QT_END_I = 12
			SET @SHIP_REQ_SCH_DT = CONVERT( DATETIME, '10/01/' +  CONVERT(VARCHAR,@SHIP_YR_I) )
		END
	END
	

	DECLARE @RESULTS_TABLE_COUNT INT	
	SELECT @RESULTS_TABLE_COUNT = COUNT(*) FROM @RESULTS_TABLE
	

	IF @RESULTS_TABLE_COUNT > 0
	BEGIN
		DELETE FROM @SEARCH_RESULTS_TABLE WHERE ProductID IN ( SELECT ProductID FROM @RESULTS_TABLE )

		UPDATE @SEARCH_RESULTS_TABLE SET Req_Sch_Date = @SHIP_REQ_SCH_DT, Shipped_Qty = 0, Unit_Cost = 0, Amount = 0,
		ShipmentMonth = @SHIP_MT_I,  ShipmentYear = @CURRENT_YEAR,  QuarterStart = @SHIP_QT_I,  QuarterEnd = @SHIP_QT_END_I,  ActYear = @CURRENT_YEAR
	
		INSERT INTO @SEARCH_RESULTS_TABLE SELECT * FROM @RESULTS_TABLE
	END
	ELSE
	BEGIN
		UPDATE @SEARCH_RESULTS_TABLE SET Req_Sch_Date = @SHIP_REQ_SCH_DT, Shipped_Qty = 0, Unit_Cost = 0, Amount = 0,
		ShipmentMonth = @SHIP_MT_I,  ShipmentYear = @CURRENT_YEAR,  QuarterStart = @SHIP_QT_I,  QuarterEnd = @SHIP_QT_END_I,  ActYear = @CURRENT_YEAR
	END

	--/*
	IF @CURRENT_QUARTER = 'ALL'
	BEGIN
		SELECT * FROM @SEARCH_RESULTS_TABLE WHERE YEAR( Req_Sch_Date ) = @CURRENT_YEAR ORDER BY Product_Type_ID, BaseProductName, Product 
	END
	ELSE
	BEGIN
		SELECT * FROM @SEARCH_RESULTS_TABLE WHERE DATEPART(Q, Req_Sch_Date ) = @CURRENT_QUARTER  AND YEAR( Req_Sch_Date ) = @CURRENT_YEAR ORDER BY Product_Type_ID, BaseProductName, Product 
	END
	--*/
	
END